#!/bin/bash
#by ydh V2.0 2016-11-02
set -e

## 检查输入日期(yyyy-MM-dd), 如果没有输入日期则默认为昨天
if [ $# -lt 1 ]; then
    LASTDAY=$(date -d last-day +%Y-%m-%d)
else
	LASTDAY=$1
fi
# 判断时间是否合法或者大于当前系统时间
temp=$(date +%Y-%m-%d)
if [ $LASTDAY \> $temp ];then
	echo "时间参数[$LASTDAY]，输入不正确，或大于当前系统时间！正确示例：$0 2016-01-01"
	exit 2
fi
echo $LASTDAY
echo "本地mysql安装路径"
MYSQL_HOME=/home/mysql/mysql-5.5.40-linux2.6-x86_64
echo "删除本地十天前的数据"
"$MYSQL_HOME/bin/mysql" -uroot bufferdb -e"delete from t_access_log_bak where access_datetime < DATE_ADD('$LASTDAY', INTERVAL -10 DAY)";
echo "删除远程数据库半年前的数据"
"$MYSQL_HOME/bin/mysql" -h172.16.4.102 -uroot -p888888 demo -e"delete from t_access_log_bak where access_datetime < DATE_ADD('$LASTDAY', INTERVAL -180 DAY)"
echo "删除远程数据库昨天的数据"
"$MYSQL_HOME/bin/mysql" -h172.16.4.102 -uroot -p888888 demo -e"delete from t_access_log_bak where access_datetime >= '$LASTDAY' and access_datetime < DATE_ADD('$LASTDAY', INTERVAL 1 DAY)"
echo "用数据泵把本地昨天的数据推送到远程数据库"
"$MYSQL_HOME/bin/mysqldump" -t -uroot bufferdb t_access_log_bak --where=" access_datetime >=  '$LASTDAY' and access_datetime < DATE_ADD('$LASTDAY', INTERVAL 1 DAY)" | "/home/mysql/mysql-5.5.40-linux2.6-x86_64/bin/mysql" -h172.16.4.102 -uroot -p888888 demo
echo "调用远程数据库统计存储过程"
#"$MYSQL_HOME/bin/mysql" -h172.16.4.102 -uroot -p888888 demo -e"call myProcedure"
echo "删除本地昨天的统计数据"
#"$MYSQL_HOME/bin/mysql" -uroot bufferdb -e"delete from t_statist where access_datetime >=  '$LASTDAY' and access_datetime < DATE_ADD('$LASTDAY', INTERVAL 1 DAY)";
echo "将远程数据库统计数据拉取到本地"
#"$MYSQL_HOME/bin/mysql" -h172.16.4.102 -uroot -p888888 demo t_statist --where=" access_datetime >=  '$LASTDAY' and access_datetime < DATE_ADD('$LASTDAY', INTERVAL 1 DAY)" | "/home/mysql/mysql-5.5.40-linux2.6-x86_64/bin/mysql" -uroot -p888888 t_statist
